﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Intelligent.OTC.Domain.DataModel;
using Intelligent.OTC.Domain.Repositories;
using System.Data.Entity;
using Intelligent.OTC.Business;
using Intelligent.OTC.Domain;
using Intelligent.OTC.Common;
using Intelligent.OTC.Common.Utils;
using System.Web;
using System.Configuration;
using System.IO;
using System.Net.Http;
using System.Net;
using System.Net.Http.Headers;
using Intelligent.OTC.Common.Exceptions;


namespace Intelligent.OTC.Business
{
    public class ContactCustomerService : IContactCustomerService
    {
        public OTCRepository CommonRep { get; set; }

        /// <summary>
        /// get all collector_alert
        /// </summary>
        /// <returns></returns>
        [Obsolete]
        public IEnumerable<ContactCustomerDto> GetContactCustomer(string invoiceState = "", string invoiceTrackState = "", string legalEntity = "",string invoiceNum = "", string soNum = "",string poNum = "",string invoiceMemo = "")
        {
            string deal = AppContext.Current.User.Deal.ToString();
            string eid = AppContext.Current.User.EID.ToString();
            IEnumerable<ContactCustomerDto> r = new List<ContactCustomerDto>();

            //// get period id
            //PeriodControl Period = CommonRep.GetDbSet<PeriodControl>()
            //    .Where(o => o.Deal == deal).OrderByDescending(o => o.Id).FirstOrDefault();
            //int latestPId = Period.Id;

            //if (string.IsNullOrEmpty(invoiceState) && string.IsNullOrEmpty(invoiceTrackState) && string.IsNullOrEmpty(invoiceNum) && string.IsNullOrEmpty(soNum) && string.IsNullOrEmpty(poNum) && string.IsNullOrEmpty(invoiceMemo))
            //{
            //    #region Without InvoiceState and InvoiceTrackState
            //    r = (from final in
            //             (from res in
            //                  // base table: customer
            //                  (from cust in SpringFactory.GetObjectImpl<CustomerService>("CustomerService").GetCustomerLevel(latestPId)
            //                   // left join t_mail
            //                   join m in CommonRep.GetDbSet<MailTmp>().Where(m => m.Category == "Inbox" && m.CustomerMails.Count > 0) on new { cust.CustomerNum, cust.Deal } equals new { CustomerNum = m.Bussiness_Reference, m.Deal }
            //                      into mails
            //                   // inner join aging
            //                   join age in CommonRep.GetQueryable<CustomerAging>().Where(o => o.Deal == deal && o.RemoveFlg == "0") on new { cust.CustomerNum, cust.Deal } equals new { age.CustomerNum, age.Deal }
            //                      into ages
            //                   from age in ages.DefaultIfEmpty()
            //                   //where age.LegalEntity == legalEntity || string.IsNullOrEmpty(legalEntity)
            //                   // left join hold customer table
            //                   join ch in CommonRep.GetDbSet<HoldCustomer>() on cust.CustomerNum equals ch.CustomerNum
            //                      into custHolds
            //                   select new
            //                   {
            //                       //LegalEntityList = from age in ages
            //                       //                  select age.LegalEntity,
            //                       Id = cust.Id,
            //                       Deal = cust.Deal,
            //                       CustomerNum = cust.CustomerNum,
            //                       CustomerName = cust.CustomerName,
            //                       BillGroupCode = cust.BillGroupCode,
            //                       BillGroupName = cust.BillGroupName,
            //                       Class = (string.IsNullOrEmpty(cust.ValueLevel) == true ? "LV" : cust.ValueLevel) +
            //                                 (string.IsNullOrEmpty(cust.RiskLevel) == true ? "LR" : cust.RiskLevel),
            //                       Risk = cust.Risk,
            //                       TotalAmt = age == null ? 0 : age.TotalAmt,
            //                       PastDueAmt = age == null ? 0 : age.DueoverTotalAmt,
            //                       FDueOver90Amt = age == null ? 0 : (age.Due120Amt + age.Due150Amt + age.Due180Amt + age.Due210Amt + age.Due240Amt + age.Due270Amt + age.Due300Amt + age.Due330Amt + age.Due360Amt + age.DueOver360Amt),
            //                       CreditLimit = age == null ? 0 : age.CreditLimit,
            //                       CusStatus = custHolds.Any(ch => ch.IsHoldFlg == "1") ? "1" : "0",
            //                       IsHoldFlg = custHolds.Any(ch => ch.IsHoldFlg == "1") ? "On Hold" : "Normal",
            //                       Operator = cust.Collector,
            //                       MailFlag = mails.Any() ? "1" : "0"
            //                   })
            //              group res by new
            //              {
            //                  res.Id,
            //                  res.Deal,
            //                  res.CustomerNum,
            //                  res.CustomerName,
            //                  res.BillGroupCode,
            //                  res.BillGroupName,
            //                  res.MailFlag,
            //                  res.Risk,
            //                  res.Class,
            //                  res.CusStatus,
            //                  res.IsHoldFlg,
            //                  res.Operator
            //              } into reses
            //              select new
            //              {
            //                  Id = reses.Key.Id,
            //                  Deal = reses.Key.Deal,
            //                  CustomerNum = reses.Key.CustomerNum,
            //                  CustomerName = reses.Key.CustomerName,
            //                  BillGroupCode = reses.Key.BillGroupCode,
            //                  BillGroupName = reses.Key.BillGroupName,
            //                  MailFlag = reses.Key.MailFlag,
            //                  Risk = reses.Key.Risk,
            //                  Class = reses.Key.Class,
            //                  CusStatus = reses.Key.CusStatus,
            //                  IsHoldFlg = reses.Key.IsHoldFlg,
            //                  Operator = reses.Key.Operator,
            //                  TotalAmt = reses.Sum(age => age.TotalAmt),
            //                  PastDueAmt = reses.Sum(age => age.PastDueAmt),
            //                  FDueOver90Amt = reses.Sum(age => age.FDueOver90Amt),
            //                  CreditLimit = reses.Sum(age => age.CreditLimit)
            //              })
            //         join aging in CommonRep.GetDbSet<CustomerAging>() on new { final.CustomerNum, final.Deal } equals new { aging.CustomerNum, aging.Deal }
            //             into agings
            //         where agings.Any(a => a.LegalEntity == legalEntity || string.IsNullOrEmpty(legalEntity))
            //         select new ContactCustomerDto
            //         {
            //             Id = final.Id,
            //             LegalEntityList = from aging in agings
            //                               select aging.LegalEntity,
            //             Deal = final.Deal,
            //             CustomerNum = final.CustomerNum,
            //             CustomerName = final.CustomerName,
            //             BillGroupCode = final.BillGroupCode,
            //             BillGroupName = final.BillGroupName,
            //             Class = final.Class,
            //             Risk = final.Risk,
            //             TotalAmt = final.TotalAmt,
            //             PastDueAmt = final.PastDueAmt,
            //             FDueOver90Amt = final.FDueOver90Amt,
            //             CreditLimit = final.CreditLimit,
            //             CusStatus = final.CusStatus,
            //             IsHoldFlg = final.IsHoldFlg,
            //             Operator = final.Operator,
            //             MailFlag = final.MailFlag
            //         });
            //    #endregion
            //}
            //else
            //{
            //    #region With InvoiceState Or InvoiceTrackState
            //    r = (from final in
            //             (from res in
            //                  // base table: customer
            //                  (from cust in SpringFactory.GetObjectImpl<CustomerService>("CustomerService").GetCustomerLevel(latestPId)
            //                   // left join t_mail
            //                   join m in CommonRep.GetDbSet<MailTmp>().Where(m => m.Category == "Inbox" && m.CustomerMails.Count > 0) on new { cust.CustomerNum, cust.Deal } equals new { CustomerNum = m.Bussiness_Reference, m.Deal }
            //                      into mails
            //                   // inner join aging
            //                   join age in CommonRep.GetQueryable<CustomerAging>().Where(o => o.Deal == deal && o.RemoveFlg == "0") on new { cust.CustomerNum, cust.Deal } equals new { age.CustomerNum, age.Deal }
            //                      into ages
            //                   from age in ages.DefaultIfEmpty()
            //                   //where age.LegalEntity == legalEntity || string.IsNullOrEmpty(legalEntity)
            //                   // left join hold customer table
            //                   join ch in CommonRep.GetDbSet<HoldCustomer>() on cust.CustomerNum equals ch.CustomerNum
            //                      into custHolds
            //                   // EXISTS clause
            //                   join inv in CommonRep.GetDbSet<InvoiceAging>().Where(inv => inv.Deal == deal) on new { cust.CustomerNum, cust.Deal } equals new { inv.CustomerNum, inv.Deal }
            //                      into invs
            //                   where invs.Any(
            //                        i => i.CustomerNum == cust.CustomerNum 
            //                       && (i.States == invoiceState || string.IsNullOrEmpty(invoiceState)) 
            //                       && (i.TrackStates == invoiceTrackState || string.IsNullOrEmpty(invoiceTrackState))
            //                       && (i.InvoiceNum.IndexOf(invoiceNum) >=0 || string.IsNullOrEmpty(invoiceNum)) 
            //                       && (i.SoNum.IndexOf(soNum) >= 0 || string.IsNullOrEmpty(soNum)) 
            //                       && (i.PoNum.IndexOf(poNum) >= 0 || string.IsNullOrEmpty(poNum)) 
            //                       && (i.Comments.IndexOf(invoiceMemo) >= 0 || string.IsNullOrEmpty(invoiceMemo))
            //                       )
            //                   select new
            //                   {
            //                       //LegalEntityList = from age in ages
            //                       //                  select age.LegalEntity,
            //                       Id = cust.Id,
            //                       Deal = cust.Deal,
            //                       CustomerNum = cust.CustomerNum,
            //                       CustomerName = cust.CustomerName,
            //                       BillGroupCode = cust.BillGroupCode,
            //                       BillGroupName = cust.BillGroupName,
            //                       Class = (string.IsNullOrEmpty(cust.ValueLevel) == true ? "LV" : cust.ValueLevel) +
            //                                 (string.IsNullOrEmpty(cust.RiskLevel) == true ? "LR" : cust.RiskLevel),
            //                       Risk = cust.Risk,
            //                       TotalAmt = age == null ? 0 : age.TotalAmt,
            //                       PastDueAmt = age == null ? 0 : age.DueoverTotalAmt,
            //                       FDueOver90Amt = age == null ? 0 : (age.Due120Amt + age.Due150Amt + age.Due180Amt + age.Due210Amt + age.Due240Amt + age.Due270Amt + age.Due300Amt + age.Due330Amt + age.Due360Amt + age.DueOver360Amt),
            //                       CreditLimit = age == null ? 0 : age.CreditLimit,
            //                       CusStatus = custHolds.Any(ch => ch.IsHoldFlg == "1") ? "1" : "0",
            //                       IsHoldFlg = custHolds.Any(ch => ch.IsHoldFlg == "1") ? "On Hold" : "Normal",
            //                       Operator = cust.Collector,
            //                       MailFlag = mails.Any(m => m.Id > 0) ? "1" : "0"
            //                   })
            //              group res by new
            //              {
            //                  res.Id,
            //                  res.Deal,
            //                  res.CustomerNum,
            //                  res.CustomerName,
            //                  res.BillGroupCode,
            //                  res.BillGroupName,
            //                  res.MailFlag,
            //                  res.Risk,
            //                  res.Class,
            //                  res.CusStatus,
            //                  res.IsHoldFlg,
            //                  res.Operator
            //              } into reses
            //              select new
            //              {
            //                  Id = reses.Key.Id,
            //                  Deal = reses.Key.Deal,
            //                  CustomerNum = reses.Key.CustomerNum,
            //                  CustomerName = reses.Key.CustomerName,
            //                  BillGroupCode = reses.Key.BillGroupCode,
            //                  BillGroupName = reses.Key.BillGroupName,
            //                  MailFlag = reses.Key.MailFlag,
            //                  Risk = reses.Key.Risk,
            //                  Class = reses.Key.Class,
            //                  CusStatus = reses.Key.CusStatus,
            //                  IsHoldFlg = reses.Key.IsHoldFlg,
            //                  Operator = reses.Key.Operator,
            //                  TotalAmt = reses.Sum(age => age.TotalAmt),
            //                  PastDueAmt = reses.Sum(age => age.PastDueAmt),
            //                  FDueOver90Amt = reses.Sum(age => age.FDueOver90Amt),
            //                  CreditLimit = reses.Sum(age => age.CreditLimit)
            //              })
            //         join aging in CommonRep.GetDbSet<CustomerAging>() on new { final.CustomerNum, final.Deal } equals new { aging.CustomerNum, aging.Deal }
            //             into agings
            //         where agings.Any(a => a.LegalEntity == legalEntity || string.IsNullOrEmpty(legalEntity))
            //         select new ContactCustomerDto
            //         {
            //             Id = final.Id,
            //             LegalEntityList = from aging in agings
            //                               select aging.LegalEntity,
            //             Deal = final.Deal,
            //             CustomerNum = final.CustomerNum,
            //             CustomerName = final.CustomerName,
            //             BillGroupCode = final.BillGroupCode,
            //             BillGroupName = final.BillGroupName,
            //             Class = final.Class,
            //             Risk = final.Risk,
            //             TotalAmt = final.TotalAmt,
            //             PastDueAmt = final.PastDueAmt,
            //             FDueOver90Amt = final.FDueOver90Amt,
            //             CreditLimit = final.CreditLimit,
            //             CusStatus = final.CusStatus,
            //             IsHoldFlg = final.IsHoldFlg,
            //             Operator = final.Operator,
            //             MailFlag = final.MailFlag
            //         });
            //    #endregion
            //}

            return r;
        }

        private DateTime dataConvertToDT(string strData)
        {
            DateTime dt = new DateTime();
            if (!string.IsNullOrEmpty(strData.Trim()))
            {
                return Convert.ToDateTime(strData);
            }

            return dt;
        }

        ///// <summary>
        ///// get all contact customer
        ///// </summary>
        ///// <returns></returns>
        //public IEnumerable<ContactCustomerView> GetContactCustomerFromCustomer()
        //{
        //    string deal = AppContext.Current.User.Deal.ToString();
        //    string eid = AppContext.Current.User.EID.ToString();

        //    return CommonRep.GetQueryable<ContactCustomerView>()
        //        .Where(m => m.Deal == deal && m.Operator == eid && m.MailFlag == "1").Select(m => m);
        //}

        /// <summary>
        /// Get Contact History Datas
        /// </summary>
        /// <param name="strCusNum"></param>
        /// <returns></returns>
        public IEnumerable<ContactHistory> GetContactList(string strCusNum)
        {
            return CommonRep.GetQueryable<ContactHistory>().Where(o => o.CustomerNum == strCusNum && o.Deal == AppContext.Current.User.Deal).Select(o => o).ToList();
        }

        /// <summary>
        /// Get Dispute Datas
        /// </summary>
        /// <param name="strCusNum"></param>
        /// <returns></returns>
        public IEnumerable<Dispute> GetDisputeList(string strCusNum)
        {
            return CommonRep.GetQueryable<Dispute>().Where(o => o.CustomerNum == strCusNum && o.Deal == AppContext.Current.User.Deal).Select(o => o).ToList();
        }
        //added by zhangYu contactHistory call detail
        public Call GetCallInfoByContactId(string contactId)
        {
            Call callIns = new Call();
            callIns = CommonRep.GetQueryable<Call>().Where(ca => ca.ContactId == contactId).FirstOrDefault();
            //add by pxc 
            callIns.contacterId = CommonRep.GetDbSet<ContactHistory>().Where(o => o.ContactId == contactId).FirstOrDefault().ContacterId;
            return callIns;
        }

        //added by zhangYu  call create/ write callInfo to T_call
        public void WriteCallLog(Call callInstance)
        {
            string strGUID = "";
            Call ca = new Call();
            ContactHistory con = new ContactHistory();
            List<ContactHistory> cons = new List<ContactHistory>();
            InvoiceLog invLog = new InvoiceLog();
            List<InvoiceLog> listInvLog = new List<InvoiceLog>();
            //GET GUID
            strGUID = System.Guid.NewGuid().ToString();

            //T_CALL
            ca.BeginTime = callInstance.BeginTime;
            ca.EndTime = callInstance.EndTime;
            ca.Comments = callInstance.Comments;
            ca.ContactId = strGUID;

            //T_CONTACT_HISTORY
            foreach (var cust in callInstance.customerNum.Split(','))
            {
                con = new ContactHistory();
                con.Deal = AppContext.Current.User.Deal;
                con.CustomerNum = cust;
                con.ContactType = "Call";
                con.ContactId = strGUID;
                con.CollectorId = AppContext.Current.User.EID;
                //contacter
                con.ContacterId = callInstance.contacterId;
                con.Comments = callInstance.Comments;
                //contactDate??
                con.ContactDate = AppContext.Current.User.Now;
                cons.Add(con);
            }

            //T_INVOICElOG
            if (callInstance.invoiceIds.Length > 0)
            {
                List<InvoiceAging> invos = (from invo in CommonRep.GetQueryable<InvoiceAging>()
                                            where callInstance.invoiceIds.Contains(invo.Id)
                                            select invo).ToList<InvoiceAging>();

                foreach (var invo in invos)
                {
                    invLog = new InvoiceLog();
                    //var invoAging=invos.Find(i => i.Id == invo);
                    invLog.ProofId = strGUID;
                    invLog.Deal = AppContext.Current.User.Deal;
                    invLog.InvoiceId = invo.InvoiceNum;
                    invLog.NewStatus = invo.States;
                    invLog.OldStatus = invo.States;
                    invLog.CustomerNum = invo.CustomerNum;
                    invLog.ContactPerson = callInstance.contacterId;
                    invLog.LogPerson = AppContext.Current.User.EID;
                    invLog.LogType = "2";//2:call
                    if (string.IsNullOrEmpty(callInstance.logAction))
                    { invLog.LogAction = "CONTACT"; }
                    else
                    {
                        invLog.LogAction = callInstance.logAction;
                    }
                    invLog.LogDate = AppContext.Current.User.Now;
                    listInvLog.Add(invLog);
                }
                CommonRep.AddRange<InvoiceLog>(listInvLog);
            }
            CommonRep.Add(ca);//T_call
            //CommonRep.Add(con);//T_CONTACT_HISTORY
            CommonRep.AddRange(cons);
            CommonRep.Commit();

        }//WriteCallLog


        public void UpdateCallLog(Call callInstance)
        {
            Call upCal = (from cal in CommonRep.GetQueryable<Call>()
                          where cal.ContactId == callInstance.ContactId
                          select cal).FirstOrDefault<Call>();

            ContactHistory conHis = (from ch in CommonRep.GetQueryable<ContactHistory>()
                                     where ch.ContactId == callInstance.ContactId
                                     select ch).FirstOrDefault<ContactHistory>();
            //T_CALL
            upCal.BeginTime = callInstance.BeginTime;
            upCal.EndTime = callInstance.EndTime;
            upCal.Comments = callInstance.Comments;


            //T_CONTACT_HISTORY
            conHis.ContacterId = callInstance.contacterId;
            conHis.Comments = callInstance.Comments;
            conHis.LastUpdatePerson = AppContext.Current.User.EID;
            conHis.LastUpdateTime = AppContext.Current.User.Now;
            //conHis.ContactDate = AppContext.Current.User.Now;

            CommonRep.Commit();
        }

        /// <summary>
        /// Each Collector can expoert all of its own Invoice Account
        /// </summary>
        /// <returns></returns>
        public HttpResponseMessage ExpoertInvoiceList()
        {
            List<Customer.ExpCustomerDto> lstCustomer = new List<Customer.ExpCustomerDto>();
            List<InvoiceAging> invoiceList = new List<InvoiceAging>();
            InvoiceAging invoice = new InvoiceAging();
            string templateFile = "";
            string fileName = "";
            string tmpFile = "";
            string closedStatus = Helper.EnumToCode<InvoiceStatus>(InvoiceStatus.Closed);
            string cancellStatus = Helper.EnumToCode<InvoiceStatus>(InvoiceStatus.Cancelled);

            try
            {
                //模板文件  
                templateFile = HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["ExportInvoiceTemplate"].ToString());
                fileName = AppContext.Current.User.EID + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx";
                tmpFile = Path.Combine(Path.GetTempPath(), fileName);

                //Get all collector responsible customers
                var customerSql = string.Format(@"
                    SELECT DEAL AS Deal, CUSTOMER_NUM AS CustomerNum, CUSTOMER_NAME AS CustomerName, COLLECTOR AS Collector 
                    FROM (SELECT A.DEAL, A.CUSTOMER_NUM, A.CUSTOMER_NAME, ISNULL(B.COLLECTOR, A.COLLECTOR) AS COLLECTOR 
                    FROM T_CUSTOMER AS A LEFT JOIN 
                         T_CUSTOMER_GROUP_CFG AS B ON A.DEAL = B.DEAL AND A.BILL_GROUP_CODE = B.BILL_GROUP_CODE
                    WHERE A.REMOVE_FLG = '1' and A.EXCLUDE_FLG = '0') C
                    WHERE COLLECTOR = '{0}' and DEAL = '{1}'
                ", AppContext.Current.User.EID, AppContext.Current.User.Deal);
                lstCustomer = CommonRep.GetDBContext().Database.SqlQuery<Customer.ExpCustomerDto>(customerSql)
                                .OrderBy(o => o.Deal).ThenBy(o => o.CustomerNum).ToList();

                //Get invoice list
                foreach (var cus in lstCustomer)
                {
                    List<InvoiceAging> invList = new List<InvoiceAging>();
                    invList = CommonRep.GetQueryable<InvoiceAging>()
                                        .Where(o => o.Deal == cus.Deal && o.CustomerNum == cus.CustomerNum
                                            && o.States != closedStatus
                                            && o.States != cancellStatus)
                                            .OrderBy(o => o.LegalEntity).ToList();
                    foreach (var inv in invList)
                    {
                        invoice = new InvoiceAging();
                        invoice.Id = inv.Id;
                        invoice.CustomerNum = inv.CustomerNum;
                        invoice.CustomerName = inv.CustomerName;
                        invoice.InvoiceNum = inv.InvoiceNum;
                        invoice.LegalEntity = inv.LegalEntity;
                        invoice.InvoiceDate = inv.InvoiceDate;
                        invoice.CreditTrem = inv.CreditTrem;
                        invoice.DueDate = inv.DueDate;
                        invoice.PoNum = inv.PoNum;
                        invoice.SoNum = inv.SoNum;
                        invoice.MstCustomer = inv.MstCustomer;
                        invoice.Currency = inv.Currency;
                        invoice.OriginalAmt = inv.OriginalAmt;
                        invoice.BalanceAmt = inv.BalanceAmt;
                        invoice.DaysLateSys = int.Parse(new TimeSpan(AppContext.Current.User.Now.Ticks).Subtract(new TimeSpan(Convert.ToDateTime(inv.DueDate).Ticks)).Duration().Days.ToString());
                        invoice.TrackStates = !String.IsNullOrEmpty(inv.TrackStates) ? Helper.CodeToEnum<TrackStatus>(inv.TrackStates).ToString().Replace("_", " ") : "";
                        invoice.States = !String.IsNullOrEmpty(inv.States) ? Helper.CodeToEnum<InvoiceStatus>(inv.States).ToString() : "";
                        invoice.PtpDate = inv.PtpDate;
                        invoice.Class = inv.Class;
                        invoice.Comments = inv.Comments;
                        invoiceList.Add(invoice);
                    }
                }

                this.setData(templateFile, tmpFile, invoiceList);

                HttpResponseMessage response = new HttpResponseMessage();
                response.StatusCode = HttpStatusCode.OK;
                MemoryStream fileStream = new MemoryStream();
                if (File.Exists(tmpFile))
                {
                    using (FileStream fs = File.OpenRead(tmpFile))
                    {
                        fs.CopyTo(fileStream);
                    }
                }
                else
                {
                    throw new OTCServiceException("Get file failed because file not exist with physical path: " + tmpFile);
                }
                Stream ms = fileStream;
                ms.Position = 0;
                response.Content = new StreamContent(ms);
                response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment")
                {
                    FileName = fileName
                };
                response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream");
                response.Content.Headers.ContentLength = ms.Length;

                return response;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                //File.Delete(tmpFile);
            }
        }

        #region Set Invoice Report Datas
        private void setData(string templateFileName, string tmpFile, List<InvoiceAging> lstDatas)
        {
            int rowNo = 1;

            try
            {
                NpoiHelper helper = new NpoiHelper(templateFileName);
                helper.Save(tmpFile, true);
                helper = new NpoiHelper(tmpFile);
                string sheetName = "";

                foreach (string sheet in helper.Sheets)
                {
                    sheetName = sheet;
                    break;
                }

                //设置sheet
                helper.ActiveSheetName = sheetName;

                //设置Excel的内容信息
                foreach (var lst in lstDatas)
                {
                    helper.SetData(rowNo, 0, lst.CustomerNum);
                    helper.SetData(rowNo, 1, lst.CustomerName);
                    helper.SetData(rowNo, 2, lst.LegalEntity);
                    helper.SetData(rowNo, 3, lst.InvoiceNum);
                    helper.SetData(rowNo, 4, string.IsNullOrEmpty(lst.InvoiceDate.ToString()) ? "" : lst.InvoiceDate.Value.ToString("yyyy-MM-dd"));
                    helper.SetData(rowNo, 5, lst.CreditTrem);
                    helper.SetData(rowNo, 6, string.IsNullOrEmpty(lst.DueDate.ToString()) ? "" : lst.DueDate.Value.ToString("yyyy-MM-dd"));
                    helper.SetData(rowNo, 7, lst.PoNum);
                    helper.SetData(rowNo, 8, lst.SoNum);
                    helper.SetData(rowNo, 9, lst.MstCustomer);
                    helper.SetData(rowNo, 10, lst.Currency);
                    helper.SetData(rowNo, 11, lst.OriginalAmt);
                    helper.SetData(rowNo, 12, lst.BalanceAmt);
                    helper.SetData(rowNo, 13, lst.DaysLateSys);
                    helper.SetData(rowNo, 14, lst.TrackStates);
                    helper.SetData(rowNo, 15, lst.States);
                    helper.SetData(rowNo, 16, string.IsNullOrEmpty(lst.PtpDate.ToString()) ? "" : lst.PtpDate.Value.ToString("yyyy-MM-dd"));
                    helper.SetData(rowNo, 17, lst.Class);
                    helper.SetData(rowNo, 18, lst.Comments);

                    rowNo++;
                }

                //formula calcuate result
                helper.Save(tmpFile, true);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion
    }
}
